* PUT ALL THE CANTONAL AND MUNICIPAL REVENUE DATA TOGETHER 

version 14.2

clear
cap log close 
cap clear matrix
set more off
graph drop _all


cd "$mypathRR"

* GET CANTONAL TAX REVENUES
* infile the stuff from excel "Finanzhaushalte"
local i=0 // set counter
foreach cant in zh be lu ur sz ow nw gl zg fr so bs bl sh ar ai sg gr ag tg ti vd vs ne ge ju {
local i=`i'+1
import excel "Datasets/Finanzstatistik/ktn_gdn_`cant'.xlsx", sheet("einnahmen") cellrange(A9:AC22) allstring
gen canton=`i'
tempfile `cant'
save "``cant''", replace
clear
}

* put the cantons together
use "`zh'", clear
foreach cant in be lu ur sz ow nw gl zg fr so bs bl sh ar ai sg gr ag tg ti vd vs ne ge ju {
append using "``cant''"
}

* attach cant value labels
run "Resources/labels-cantons.do"
label values cant cant


* sort out data
destring A, replace
labmask A, values(B) // this takes the values of B and use them as labels for A


* sum accross cants to get "Switzerland" as a Canton
sort A canton
local i=1989
foreach name in C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC {
local i = `i'+ 1
replace `name'="." if `name'=="–"
destring `name', replace
rename `name' rev`i'
bysort A: egen revCH`i'= sum(rev`i') // this sums up the revenues to CH-level
bysort A: egen revCentralOW`i' = sum(rev`i') if canton == 3 | canton ==4 | canton == 5 | canton == 6  | canton == 7 | canton == 9 // this sums up central CH
bysort A: egen revCentral`i' = sum(rev`i') if canton == 3 | canton ==4 | canton == 5 | canton == 7 | canton == 9 // this sums up central CH excl. OW
}

drop B

* collapse data 
preserve 
keep A revCH1990 - revCH2016
collapse (mean) revCH1990 - revCH2016, by(A)
gen canton=0
forvalues x=1990(1)2016 {
rename revCH`x' rev`x'
}
tempfile partCH
save "`partCH'", replace
restore

preserve 
keep A revCentral1990 - revCentral2016
collapse (mean) revCentral1990 - revCentral2016, by(A)
gen canton=28
forvalues x=1990(1)2016 {
rename revCentral`x' rev`x'
}
tempfile centralCH
save "`centralCH'", replace
restore


preserve 
keep A revCentralOW1990 - revCentralOW2016
collapse (mean) revCentralOW1990 - revCentralOW2016, by(A)
gen canton=29
forvalues x=1990(1)2016 {
rename revCentralOW`x' rev`x'
}
tempfile centralCHOW
save "`centralCHOW'", replace
restore

append using "`partCH'"
format  revCH1990 - revCH2016 %10.0g

append using "`centralCH'"
format  revCentral1990 - revCentral2016 %10.0g

append using "`centralCHOW'"
format  revCentralOW1990 - revCentralOW2016 %10.0g


label define cant 28 `"central CH - excl. OW"', modify
label define cant 29 `"central CH"', modify
label values canton cant




* reshape data
reshape long rev revCH revCentral revCentralOW, i(canton A) j(year)
replace revCH=rev if revCH==. 

// make sure revCentral is not missing
sort year A
foreach var in revCentral revCentralOW {
bys A year: replace `var'=`var'[_n-1] if `var'==.
bys year A: replace `var'=`var'[_n-1] if `var'==.
bys year A: replace `var'=`var'[_n+1] if `var'==.
bys year A: replace `var'=`var'[_n+1] if `var'==.
bys year A: replace `var'=`var'[_n+1] if `var'==.
}

sort year canton A

reshape wide rev revCH revCentral revCentralOW, i(canton year) j(A)

// set english labels, but save the german ones currently in use too
label language ger, new
label language eng, new


foreach var in rev revCH revCentral revCentralOW {
label var	`var'4		"Total revenue (in 1000 CHF)"	
label var	`var'40		"Total fiscal revneue (in 1000 CHF)"	
label var	`var'400	"Total direct personal taxes (in 1000 CHF)"	
label var	`var'4000	"Personal income tax revenue (in 1000 CHF)"	
label var	`var'4001	"Personal wealth tax revenue (in 1000 CHF)"	
label var	`var'4002	"Source tax revenue (in 1000 CHF)"	
label var	`var'4008	"Head taxes (in 1000 CHF)"

label var	`var'4009	"Other direct personal taxes (in 1000 CHF)"
label var	`var'401	"Total direct corporate taxes (in 1000 CHF)"	
label var	`var'4010	"Corporate income tax revenue (in 1000 CHF)"
label var	`var'4011	"Corporate capital tax revenue (in 1000 CHF)"	
label var	`var'4012	"Corporate source tax revenue (in 1000 CHF)"	
label var	`var'4019	"Other direct corporate taxes (in 1000 CHF)"	

label var	`var'402	"Other direct taxes (in 1000 CHF)"	


rename 	`var'4		`var'_all_rev	
rename 	`var'40		`var'_tot_fiscalrev
rename 	`var'400	`var'_total
rename 	`var'4000	`var'_inctax	
rename 	`var'4001   `var'_weatax	
rename	`var'4002	`var'_sourcetax	

rename 	`var'401	`var'_corp_total
rename 	`var'4010	`var'_corp_inctax
rename 	`var'4011   `var'_corp_captax
rename	`var'4012	`var'_corp_sourcetax	
rename	`var'4019	`var'_corp_other	

rename 	`var'402	`var'_other_direct

}

compress
note: Source Federal Finance Administration "https://www.efv.admin.ch/efv/de/home/themen/finanzstatistik/daten.html#118810996"
save "Datasets/revenuedata_ktn+gde.dta", replace

